﻿
Imports ccapps.Web.page

Imports System.Data.OracleClient

Namespace persistent


    Public Class Promotions

        Private Shared ConnStr As String = GlobalVar.OraConnStr

        Private Shared dbPackageName As String = "EU_BILL.CustomerPromotions."



        Public Shared Function GetCustInfoFromCustcode(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromCustcode")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New CustomerDetails2()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(GetCustInfoFromCustcode)", "Promotions", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function
        Public Shared Function GetCustInfoFromMSISDN(ByVal pin_MSISDN As String) As DataTable
            'pin_MSISDN = "90" + pin_MSISDN
            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromMSISDN")
            command.Parameters.Add("pin_MSISDN", OracleType.VarChar).Value = pin_MSISDN
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

            ''LOGLAMA
            'Dim log_msisdn As String = pin_MSISDN.Substring(2, 10)
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New CustomerDetails2()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_MSISDN, "Select(GetCustInfoFromMSISDN)", "Promotions", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetCustomerPromotionList(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustPromotionListActive")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New CustomerDetails2()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", pin_customer_id, "null", "null", "Select(GetCustomerPromotionList)", "Promotions", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function


        Public Shared Function GetCustomerPromotionListDeactive(ByVal pin_customer_id As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustPromotionListDeactive")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New CustomerDetails2()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", pin_customer_id, "null", "null", "Select(GetCustomerPromotionListDeactive)", "Promotions", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function
        Public Shared Function DisplayOCCHistoryWithCOID(ByVal pin_customer_id As Integer, ByVal pin_CO_ID As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetOCCFromCOID")
            command.Parameters.Add("pin_customer_id", OracleType.Int32).Value = pin_customer_id
            command.Parameters.Add("pin_CO_ID", OracleType.Int32).Value = pin_CO_ID
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New CustomerDetails2()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", pin_customer_id, pin_CO_ID, "null", "Select(DisplayOCCHistoryWithCOID)", "Promotions", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function

        Public Shared Function GetCustomerPromotionDetail(ByVal pin_customer_id As String, ByVal pin_promo_pack_id As String, ByVal pin_assign_seq As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustomerPromotionDetail")
            command.Parameters.Add("pin_customer_id", OracleType.VarChar).Value = pin_customer_id
            command.Parameters.Add("pin_promo_pack_id", OracleType.VarChar).Value = pin_promo_pack_id
            command.Parameters.Add("pin_assign_seq", OracleType.VarChar).Value = pin_assign_seq
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            ''LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New CustomerDetails2()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", pin_customer_id, "null", "null", "Select(GetCustomerPromotionDetail)", "Promotions", gen.Session("user"))

            Return ExecuteDataTable(command)

        End Function


        Public Shared Function ExecuteNonQuery(ByVal command As OracleCommand, ByRef MESSAGE As String)
            Dim connection As New OracleConnection(ConnStr)
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure

            Try
                connection.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                MESSAGE = ex.Message.ToString
            Finally
                connection.Close()
            End Try
        End Function
        Public Shared Function ExecuteDataTable(ByVal command As OracleCommand, Optional ByVal directSql As Boolean = 0) As DataTable
            Dim connection As New OracleConnection(ConnStr)
            Dim dataAdapter As New OracleDataAdapter(command)
            Dim dataTable As New DataTable
            Dim dataSet As New DataSet

            command.Connection = connection

            If Not (directSql) Then
                command.CommandType = CommandType.StoredProcedure
            End If

            Try
                connection.Open()
                dataAdapter.Fill(dataSet)
                dataTable = dataSet.Tables(0)
            Catch ex As Exception
            Finally
                connection.Close()
                dataSet.Dispose()
            End Try

            Return dataTable
        End Function




    End Class
End Namespace


